"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 导入导出、备份恢复工具支持M*兼容性
Case Name   : 创建兼容M*存储过程语句，导出再导入
Create At   : 2023.05.23
Owner       : z@wan005
Description :
    1.创建兼容b库
    2.创建用户
    3.创建兼容M*存储过程，含参数definer、 language plpgsql、deterministic、
    not deterministic、contains sql、 no sql、reads sql data、
    modifies sql data、sql security definer、sql security invoker
    4.创建存储过程支持select子句
    5.创建存储过程，含repeat
    6.创建存储过程，含return
    7.创建存储过程，含declare cursor
    8.创建存储过程，含参数declare condition
    9.创建存储过程，存储体为do statement
    10.创建存储过程，存储体含 iterate label
    11.创建存储过程，存储体含case when condition
    12.创建存储过程，存储体含while
    13.查询所有存储过程和函数
    14.使用gs_dump工具导出表为纯文本格式
    15.gsql导入
    16.连接导入库查询
    17.连接导入库使用show create procedur语句查询存储过程
    18.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.成功
    8.成功
    9.成功
    10.成功
    11.成功
    12.成功
    13.成功
    14.成功
    15.成功
    16.和导入前存储过程一致
    17.成功
    18.成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class GsDump0059(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.dbuser_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = "db_dump_0059"
        self.user_name = "user_dump_0059"
        self.pro_name = "pro_dump_0059"
        self.tb_name = "tb_dump_0059"
        self.dump_path = os.path.join(macro.DB_BACKUP_PATH, 'dump0059')

    def test_server_tools(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        text = '----step1: 创建兼容b库 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"drop database if exists {self.db_name}_01;" \
                  f"create database {self.db_name} dbcompatibility 'b';" \
                  f"create database {self.db_name}_01 dbcompatibility 'b';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_result,
                      '执行失败:' + text)

        text = '--step2: 创建用户 expect: 成功--'
        self.log.info(text)
        sql_cmd = f"drop user if exists {self.user_name};" \
                  f"create user {self.user_name} password " \
                  f"'{macro.PASSWD_INITIAL}';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step3: 创建兼容M*存储过程，含参数definer、 language plpgsql、' \
               'deterministic、not deterministic、contains sql、 no sql、' \
               'reads sql data、 modifies sql data、sql security definer、' \
               'sql security invoker expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop procedure if exists {self.pro_name};
        create definer = {self.user_name} procedure \
        {self.pro_name}() language plpgsql \
        BEGIN \
        END;
        drop procedure if exists {self.pro_name}_01;
        create definer = {self.user_name} procedure {self.pro_name}_01() \
        deterministic
            BEGIN
            END;
        drop procedure if exists {self.pro_name}_02;
        create definer = {self.user_name} procedure {self.pro_name}_02() \
        not deterministic
            BEGIN
            END;
        drop procedure if exists {self.pro_name}_03;
        create definer = {self.user_name} procedure {self.pro_name}_03() \
        contains sql
            BEGIN
            END;
        drop procedure if exists {self.pro_name}_04;
        create definer = {self.user_name} procedure {self.pro_name}_04() no sql
            BEGIN
            END;
        drop procedure if exists {self.pro_name}_05;
        create definer = {self.user_name} procedure {self.pro_name}_05() \
        reads sql data
            BEGIN
            END;
        drop procedure if exists {self.pro_name}_06;
        create definer = {self.user_name} procedure {self.pro_name}_06() \
        modifies sql data
            BEGIN
            END;
        drop procedure if exists {self.pro_name}_07;
        create definer = {self.user_name} procedure {self.pro_name}_07() \
        sql security definer
            BEGIN
            END;
        drop procedure if exists {self.pro_name}_08;
        create definer = {self.user_name} procedure {self.pro_name}_08() \
        sql security invoker
            BEGIN
            END;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(9, sql_result.count(
            self.constant.CREATE_PROCEDURE_SUCCESS_MSG), '执行失败:' + text)

        text = '----step4: 创建存储过程支持select子句 expect:成功---'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name};" \
                  f"create table {self.tb_name}(c int);" \
                  f"insert {self.tb_name} values(1),(2),(3);" \
                  f"drop procedure if exists {self.pro_name}_09;" \
                  f"create definer = {self.user_name} procedure " \
                  f"{self.pro_name}_09() select c from {self.tb_name} " \
                  f"order by c;"
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn('INSERT 0 3', sql_result, '执行失败:' + text)
        self.assertIn('CREATE PROCEDURE', sql_result, '执行失败:' + text)

        text = '----step5: 创建存储过程，含repeat expect:成功---'
        self.log.info(text)
        sql_cmd = f'''drop procedure if exists {self.pro_name}_10;
        create definer = {self.user_name} procedure {self.pro_name}_10(c int)
        AS
        DECLARE
                i int = 0;
        BEGIN
                repeat
                        i = i + 1;
                        until i > c
                end repeat;
        END;
        drop procedure if exists {self.pro_name}_11;
        create definer = {self.user_name} procedure {self.pro_name}_11(c int)
        AS
        DECLARE
                i int = 0;
        BEGIN
                <<label>> repeat
                        i = i + 1;
                        until i > c
                end repeat label;
        END;'''
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)

        text = '----step6: 创建存储过程，含return expect:成功---'
        self.log.info(text)
        sql_cmd = f'''drop procedure if exists {self.pro_name}_12;
        create definer = {self.user_name} procedure 
        {self.pro_name}_12()
            BEGIN
                    return\;
            END;
        drop procedure if exists {self.pro_name}_13;
        create definer = {self.user_name} procedure \
        {self.pro_name}_13(c out int)
            BEGIN
                return c\;
            END;'''
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(2, sql_result.count(
            self.constant.CREATE_PROCEDURE_SUCCESS_MSG), '执行失败:' + text)

        text = '----step7: 创建存储过程，含declare cursor expect:成功---'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_01;
        create table {self.tb_name}_01(name varchar(100), loc varchar(100), \
        no integer);
        insert {self.tb_name}_01 values('macrosoft', 'usa', 001);
        insert {self.tb_name}_01 values('oracle', 'usa', 002);
        insert {self.tb_name}_01 values('backbery', 'canada', 003);
        drop procedure if exists {self.pro_name}_14;
        create definer = {self.user_name} procedure {self.pro_name}_14
        as
                company_name varchar(100);
                company_loc  varchar(100);
                company_no integer;
        begin
                declare c1_all cursor is
                select name, loc, no from {self.tb_name}_01 order by 1, 2, 3;
                if not c1_all%isopen then open c1_all;
                end if;
                loop
                    fetch c1_all into company_name, company_loc, company_no;
                    exit when c1_all%notfound;
                    raise notice '% : % : %', company_name, company_loc, \
                    company_no;
                end loop;
                if c1_all%isopen then
                        close c1_all;
                end if;
        end;'''
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result)
        self.assertEqual(3, sql_result.count('INSERT 0 1'))
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, sql_result)

        text = '---step8: 创建存储过程，含参数declare condition expect: 成功----'
        self.log.info(text)
        sql_cmd = f'''drop procedure if exists {self.pro_name}_15;
        create definer = {self.user_name} procedure {self.pro_name}_15
        AS
        BEGIN
                declare DISVISION_ZERO condition for 1;
                raise notice 'declare condition successed';
        END;'''
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, sql_result)

        text = '---step9: 创建存储过程，存储体为do statement expect: 成功----'
        self.log.info(text)
        sql_cmd = f'''drop procedure if exists {self.pro_name}_16;
        create definer = {self.user_name} procedure {self.pro_name}_16
        AS
        DECLARE
                x,y,z int =1;
        BEGIN
                y := 2;
                z := 4;
                do x + x;
                do y * z;
                do x + x, y * z;
                do sin(x);
                do pg_sleep(1);
        END;'''
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, sql_result)

        text = '---step10: 创建存储过程，存储体含 iterate label expect: 成功----'
        self.log.info(text)
        sql_cmd = f'''drop procedure if exists {self.pro_name}_17;
        create definer = {self.user_name} procedure {self.pro_name}_17(c int)
        AS
        BEGIN
                label1: loop
                c := c + 1;
                if c < 10 then iterate label1;
                end if;
                leave label1;
                end loop label1;
                raise notice 'c:%', c;
        END;
        drop procedure if exists {self.pro_name}_18;
        create definer = {self.user_name} procedure {self.pro_name}_18(c int)
            AS
            BEGIN
                    loop
                            c := c + 1;
                            if c < 10 then iterate;
                            end if;
                            leave;
                    end loop;
                    raise notice 'c:%', c;
            END;'''
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, sql_result)

        text = '---step11: 创建存储过程，存储体含case when condition expect: 成功----'
        self.log.info(text)
        sql_cmd = f'''drop procedure if exists {self.pro_name}_19;
        create definer = {self.user_name} procedure  {self.pro_name}_19(c int)
            AS
            BEGIN
                    case c
                            when 1 then raise notice 'one';
                            when 2 then raise notice'two';
                            when 3 then raise notice 'three';
                            else
                                    raise notice 'more than three';
                    end case;
            END;'''
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, sql_result)

        text = '---step12: 创建存储过程，存储体含while expect: 成功----'
        self.log.info(text)
        sql_cmd = f'''drop procedure if exists {self.pro_name}_20;
        create definer = {self.user_name} procedure {self.pro_name}_20(c int)
            AS
            BEGIN
                    while c < 10
                            do c := c + 1;
                    end while;
                    raise notice 'c:%', c;
            END;
            drop procedure if exists {self.pro_name}_21;
        create definer = {self.user_name} procedure {self.pro_name}_21(c int)
            AS
            BEGIN
                    label: while c < 10
                            do c := c + 1;
                    end while label;
                    raise notice 'c:%', c;
            END;'''
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, sql_result)

        text = '---step13: 查询所有存储过程和函数 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"select proname from pg_proc where proname " \
                  f"like '%{self.pro_name}%';"
        sql_result1 = self.sh_primary.execut_db_sql(sql_cmd,
                                                    dbname=self.db_name)
        self.log.info(sql_result1)
        self.assertIn('22 rows', sql_result1, '执行失败' + text)

        text = '----step14: 使用gs_dump工具导出表为纯文本格式 expect: 成功----'
        self.log.info(text)
        gs_dump_cmd = f"source {macro.DB_ENV_PATH};" \
                      f"gs_dump " \
                      f"-p {self.dbuser_node.db_port} " \
                      f"{self.db_name} -F p " \
                      f"-f {self.dump_path}"
        self.log.info(gs_dump_cmd)
        dump_msg = self.dbuser_node.sh(gs_dump_cmd).result()
        self.log.info(dump_msg)
        self.assertIn(f"dump database {self.db_name} successfully",
                      dump_msg, '执行失败:' + text)

        text = '----step15: gsql导入 expect: 成功----'
        self.log.info(text)
        gsql_cmd = f"source {macro.DB_ENV_PATH};" \
                   f"gsql -p {self.dbuser_node.db_port} " \
                   f"{self.db_name}_01 " \
                   f"-f {self.dump_path}"
        self.log.info(gsql_cmd)
        gsql_msg = self.dbuser_node.sh(gsql_cmd).result()
        self.log.info(gsql_msg)
        self.assertEqual(2, gsql_msg.count('CREATE TABLE'), '执行失败:' + text)
        self.assertEqual(22, gsql_msg.count('CREATE PROCEDURE'),
                         '执行失败:' + text)

        text = '----step16: 连接导入库查询 expect: 和导入前存储过程一致----'
        self.log.info(text)
        sql_cmd = f"show procedure status like '%{self.pro_name}%';"
        self.log.info(sql_cmd)
        sql_res2 = self.sh_primary.execut_db_sql(sql_cmd,
                                                 dbname=f'{self.db_name}_01')
        self.log.info(sql_res2)
        self.assertIn('22 rows', sql_res2, '执行失败' + text)

        text = '----step17: 连接导入库使用show create procedur语句查询存储过程 ' \
               'expect: 成功----'
        self.log.info(text)
        sql_cmd = f"show create procedure {self.pro_name};"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn(
            f'CREATE DEFINER = {self.user_name} PROCEDURE '
            f'public.{self.pro_name}',
            sql_result, '执行失败' + text)

    def tearDown(self):
        text = '---step18: 清理环境 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"drop database if exists {self.db_name}_01;" \
                  f"drop user if exists {self.user_name};"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname='postgres')
        self.log.info(sql_result)
        rm_cmd = f"rm -rf {self.dump_path}"
        self.log.info(rm_cmd)
        rm_msg = self.dbuser_node.sh(rm_cmd).result()
        self.log.info(rm_msg)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertEqual('', rm_msg)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
